Oracle Function

Function 需要有回傳值

1. 如何呼叫

   SELECT GET_EMPLOYEENAME('A12345') FROM DUAL;
   

2. 架構

   CREATE OR REPLACE FUNCTION GET_EMPLOYEENAME(
   IN_EMPLOYEEID IN VACHAR2
   )
   RETURN VACHAR2
   IS
       V_NAME VACHAR2(20);
   BEGIN
       SELECT EMPLOYEES.NAME 
       INTO V_NAME
       FROM EMPLOYEES 
       WHERE EMPLOYEES.ID = IN_EMPLOYEEID;
       RETURN V_NAME
   END GET_EMPLOYEENAME;
   

Oracle Procedure

Procedure 無回傳值

1. 如何呼叫

   EXECUTE DELETE_EMPLOYEENAME('A12345');
   

2. 架構

   CREATE OR REPLACE PROCEDURE DELETE_EMPLOYEENAME (
       IN_EMPLOYEEID IN VACHAR2
   ) 
   IS
   BEGIN
       DELETE FROM EMPLOYEES
       WHERE EMPLOYEES.ID = IN_EMPLOYEEID;
       COMMIT;
   END;
   

Oracle Package （集合 Function、Procedure）分為 Spec、Body

• Spec為 Package Interface定義 Function、Procedure
• Body為 Package 的實作